package cn.com.headfree.expert.dao.impl;

import cn.com.headfree.entity.*;
import cn.com.headfree.expert.dao.IUserDataInfoDao1;
import cn.com.headfree.vo.*;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.util.StringUtil;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

@Repository("IUserDataInfoDao1")
public class UserDataInfoDaoImpl1 extends BaseDaoImpl<TEmpBaseInfo> implements IUserDataInfoDao1 {

	@Override
	public List<TYearEffectInfo> findPageYearEffectByCondi(TYearEffectInfo tYearEffectInfo) {
		String sql = "  select t.* " +
				" from t_year_effect_info t  where t.is_valid='0' and t.del_sign = '0' and t.rate_year>year(now())-5" ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(tYearEffectInfo.getErpNo())) {
			sqlBuilder.append(" AND erp_no= ? ");
			arrayList.add(tYearEffectInfo.getErpNo());
		}

		sqlBuilder.append(" order by rate_year desc  ");
		return this.findAllBySQL(TYearEffectInfo.class, sqlBuilder.toString(), null,false, arrayList.toArray());
	}

	@Override
	public List<YearEffectVo> getYearEffectByErpNo(String erpNo, String mouldId, String ruleId, Integer year,String orgId,int index) {
		StringBuilder sqlBuilder = new StringBuilder("SELECT b.id,b.YEAR,b.perfor_grade,sum( point ) point from(SELECT a.id,a.YEAR,a.perfor_grade,a.point from(SELECT t.id,t.rate_year as year,GET_DIC('PerformLevelRecentThreeYears',perfor_grade) as perfor_grade,e.point from t_year_effect_info t ");
		sqlBuilder.append(" LEFT JOIN t_rule_element_info e on t.perfor_grade = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? ");
		sqlBuilder.append(" where t.is_valid='0' and t.del_sign = '0' ");
		List<Object> arryList = new ArrayList<Object>();
		arryList.add(mouldId);
		arryList.add(orgId);
		arryList.add(ruleId);
		sqlBuilder.append(" AND t.rate_year = ? ");
		arryList.add(year);
		sqlBuilder.append(" AND t.erp_no = ?");
		arryList.add(erpNo);
		sqlBuilder.append("   ORDER BY t.create_time DESC ) a  LIMIT 1 )b GROUP BY b.id,b.YEAR,b.perfor_grade");
		//arryList.add(index);
		return this.findAllBySQL(YearEffectVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}


	@Override
	public List<TContiStudyInfo> findPageContiStudyInfoByCondi(TContiStudyInfo tContiStudyInfo) {
		String sql = "   select t.* " +
				" from t_conti_study_info t where t.is_valid='0'  and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(tContiStudyInfo.getErpNo())) {
			sqlBuilder.append(" AND erp_no= ? ");
			arrayList.add(tContiStudyInfo.getErpNo());
		}

		sqlBuilder.append(" order by start_time desc  ");
		return this.findAllBySQL(TContiStudyInfo.class, sqlBuilder.toString(), null,false, arrayList.toArray());

	}


	@Override
	public List<TSdtTutorPrentInfo> findPageSdtTutorPrentInfoByCondi(TSdtTutorPrentInfo tSdtTutorPrentInfo) {
		String sql = "   select * from t_sdt_tutor_prent_info t where t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(tSdtTutorPrentInfo.getErpNo())) {
			sqlBuilder.append(" AND erp_no= ? ");
			arrayList.add(tSdtTutorPrentInfo.getErpNo());
		}
		sqlBuilder.append(" order by start_date desc  ");
		return this.findAllBySQL(TSdtTutorPrentInfo.class, sqlBuilder.toString(), null,false, arrayList.toArray());
	}

	@Override
	public List<TSdtPrentInfo> getTSdtPrentInfoByMids(List<String> mainIds){

		if(mainIds.isEmpty()){
			return null;
		}
		String idsStr="";
		for(int i=0;i<mainIds.size();i++){
			idsStr+=",'"+mainIds.get(i)+"'";
		}

		idsStr=idsStr.substring(1);


//		String sql =" select t.id,t.sdt_id,t.erp_no,p.user_name,t.id_card_no,t.win_info,t.award_name, " +
//				" t.creater_id,t.creater_name,t.create_time,t.updater_id,t.updater_name,t.update_time,t.del_sign, " +
//				" t.orignal_id,t.flow_status,t.business_type_id,t.isc_user_id from t_sdt_prent_info t left join isc_user i on t.erp_no=i.fax " ;

		String sql =" select * from t_sdt_prent_info " ;

		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" where sdt_id in (").append(idsStr).append(")  order by erp_no ");

		return this.findAllBySQL(TSdtPrentInfo.class, sqlBuilder.toString(), null, false,null);

	}

	@Override
	public List<TSdtAward> getTSdtAwardByMids(List<String> mainIds) {
		if(mainIds.isEmpty()){
			return null;
		}
		String idsStr="";
		for(int i=0;i<mainIds.size();i++){
			idsStr+=",'"+mainIds.get(i)+"'";
		}

		idsStr=idsStr.substring(1);
		String sql =" select * from t_sdt_award " ;

		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" where sdt_td_id in (").append(idsStr).append(")  order by item_type ");

		return this.findAllBySQL(TSdtAward.class, sqlBuilder.toString(), null, false,null);
	}


	@Override
	public List<THonorInfo> findPageHonorInfoByCondi( THonorInfo tHonorInfo) {

		String sql = " select t.* " +
				" from t_honor_info t where t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();
		if (!StringUtil.isNullOrEmpty(tHonorInfo.getErpNo())) {
			sqlBuilder.append(" AND erp_no= ? ");
			arrayList.add(tHonorInfo.getErpNo());
		}

		sqlBuilder.append(" order by acqu_time desc  ");
		return this.findAllBySQL(THonorInfo.class, sqlBuilder.toString(), null,false, arrayList.toArray());
	}

	@Override
	public  List<TPartRank> getTPartRankByMids(Set<String> mainIds, String type){
		if(mainIds.isEmpty()||StringUtil.isNullOrEmpty(type)){
			return null;
		}
		String idsStr="";
//		for(int i=0;i<mainIds.size();i++){
//			idsStr+=",'"+mainIds.get(i)+"'";
//		}
		for(String id:mainIds){
			idsStr+=",'"+id+"'";
		}

		idsStr=idsStr.substring(1);

		String jsCode="";
		if(type.equals("17")){
			jsCode="XiangMuKeTiJS";
		}else {
			jsCode="XueshuchengguoJs";
		}

//		String sql = " select t.id,t.erp_no,p.user_name,t.id_card_no,t.item_id,t.item_type,t.rank,t.role,t.del_sign,t.is_valid, " +
//				" t.audit_state,t.orignal_id,t.flow_status,t.business_type_id,t.isc_user_id , " +
//				" GET_DIC('"+ jsCode +"',t.role) as role_name from t_part_rank t left join isc_user i on t.erp_no=i.fax " ;

		String sql = " select t.* ,GET_DIC('"+ jsCode +"',t.role) as role_name from t_part_rank t  " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" where item_id in (").append(idsStr).append(") and item_type='").append(type).append("' order by item_type, rank ");
		return this.findAllBySQL(TPartRank.class, sqlBuilder.toString(), null, false,null);

	}


	@Override
	public List<TAcadeAchiveInfoVo> findPageAcadeAchiveInfoByCondi(TAcadeAchiveInfo tAcadeAchiveInfo) {
		String sql = " select t.*,r.role,r.rank,r.erp_no " +
				" from t_part_rank r , t_acade_achive_info t " +
				" where r.item_id=t.id and r.item_type='12' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();

		StringBuilder erp_no_sb= new StringBuilder();

		if(!tAcadeAchiveInfo.gettPartRankList().isEmpty()){
			tAcadeAchiveInfo.gettPartRankList().forEach(tPartRank->{
				if(!StringUtil.isNullOrEmpty(tPartRank.getErpNo())){
					erp_no_sb.append(",'").append(tPartRank.getErpNo()).append("'");
				}
			});
		}

		if(!StringUtil.isNullOrEmpty(erp_no_sb.toString())){
			sqlBuilder.append(" AND r.erp_no in ( ").append(erp_no_sb.toString().substring(1)).append(" )");
			//arrayList.add(erp_no_sb.toString().substring(1));
		}
		sqlBuilder.append(" order by pub_time desc ");
		return  this.findAllBySQL(TAcadeAchiveInfoVo.class, sqlBuilder.toString(), null, false,null);
	}

	@Override
	public List<TStandInfoVo> findPageStandInfoByCondi( TStandInfo tStandInfo) {
		String sql = "  select t.*,r.role,r.rank,r.erp_no " +
				" from t_part_rank r , t_stand_info t  " +
				" where r.item_id=t.id and r.item_type='13' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();

		StringBuilder erp_no_sb= new StringBuilder();

		if(!tStandInfo.gettPartRankList().isEmpty()){
			tStandInfo.gettPartRankList().forEach(tPartRank->{
				if(!StringUtil.isNullOrEmpty(tPartRank.getErpNo())){
					erp_no_sb.append(",'").append(tPartRank.getErpNo()).append("'");
				}
			});
		}

		if(!StringUtil.isNullOrEmpty(erp_no_sb.toString())){
			sqlBuilder.append(" AND r.erp_no in ( ").append(erp_no_sb.toString().substring(1)).append(" )");
			//arrayList.add(erp_no_sb.toString().substring(1));
		}
		sqlBuilder.append(" order by issue_time desc ");
		return this.findAllBySQL(TStandInfoVo.class, sqlBuilder.toString(), null,false, null);
	}

	@Override
	public List<TTecStandardInfo> findPageTecStandardInfoByCondi( TTecStandardInfo tecStandardInfo){
		String sql = "  select t.*,r.role,r.erp_no" +
				" from t_part_rank r , t_tec_standard_info t  " +
				" where r.item_id=t.id and r.item_type='21' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		StringBuilder erp_no_sb= new StringBuilder();

		if(!tecStandardInfo.gettPartRankList().isEmpty()){
			tecStandardInfo.gettPartRankList().forEach(tPartRank->{
				if(!StringUtil.isNullOrEmpty(tPartRank.getErpNo())){
					erp_no_sb.append(",'").append(tPartRank.getErpNo()).append("'");
				}
			});
		}

		if(!StringUtil.isNullOrEmpty(erp_no_sb.toString())){
			sqlBuilder.append(" AND r.erp_no in ( ").append(erp_no_sb.toString().substring(1)).append(" )");
			//arrayList.add(erp_no_sb.toString().substring(1));
		}
		sqlBuilder.append(" order by update_time desc ");
		return this.findAllBySQL(TTecStandardInfo.class, sqlBuilder.toString(), null,false, null);
	}

	@Override
	public List<TTecInnovInfoVo>  findPageTecInnovInfoByCondi(TTecInnovInfo tTecInnovInfo) {
		String sql = " select t.*,r.role,r.rank,r.erp_no " +
				" from t_part_rank r , t_tec_innov_info t  " +
				" where r.item_id=t.id and r.item_type='15' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();

		StringBuilder erp_no_sb= new StringBuilder();

		if(!tTecInnovInfo.gettPartRankList().isEmpty()){
			tTecInnovInfo.gettPartRankList().forEach(tPartRank->{
				if(!StringUtil.isNullOrEmpty(tPartRank.getErpNo())){
					erp_no_sb.append(",'").append(tPartRank.getErpNo()).append("'");
				}
			});
		}

		if(!StringUtil.isNullOrEmpty(erp_no_sb.toString())){
			sqlBuilder.append(" AND r.erp_no in ( ").append(erp_no_sb.toString().substring(1)).append(" )");
			//arrayList.add(erp_no_sb.toString().substring(1));
		}
		sqlBuilder.append(" order by award_date desc ");
		return this.findAllBySQL(TTecInnovInfoVo.class, sqlBuilder.toString(), null,false, null);
	}

	@Override
	public List<TTecManInnovVo> findPageTecManInnovByCondi(TTecManInnov tTecManInnov) {
		String sql = "  select t.*,r.role,r.rank,r.erp_no,r.id tid " +
				" from t_part_rank r , t_tec_man_innov t  " +
				" where r.item_id=t.id and r.item_type='16' and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();

		StringBuilder erp_no_sb= new StringBuilder();

		if(!tTecManInnov.gettPartRankList().isEmpty()){
			tTecManInnov.gettPartRankList().forEach(tPartRank->{
				if(!StringUtil.isNullOrEmpty(tPartRank.getErpNo())){
					erp_no_sb.append(",'").append(tPartRank.getErpNo()).append("'");
				}
			});
		}

		if(!StringUtil.isNullOrEmpty(erp_no_sb.toString())){
			sqlBuilder.append(" AND r.erp_no in ( ").append(erp_no_sb.toString().substring(1)).append(" )");
			//arrayList.add(erp_no_sb.toString().substring(1));
		}
		sqlBuilder.append(" order by award_date desc ");
		return this.findAllBySQL(TTecManInnovVo.class, sqlBuilder.toString(), null,false, null);
	}

	@Override
	public List<TPatentInfoVo> findPagePatentInfoByCondi(TPatentInfo tPatentInfo) {
		String sql = "   select t.*,r.role,r.rank,r.erp_no " +
				" from t_part_rank r , t_patent_info t  " +
				" where r.item_id=t.id and r.item_type='14'  and  t.is_valid='0' and t.del_sign = '0' " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);

		ArrayList<Object> arrayList = new ArrayList<Object>();

		StringBuilder erp_no_sb= new StringBuilder();

		if(!tPatentInfo.gettPartRankList().isEmpty()){
			tPatentInfo.gettPartRankList().forEach(tPartRank->{
				if(!StringUtil.isNullOrEmpty(tPartRank.getErpNo())){
					erp_no_sb.append(",'").append(tPartRank.getErpNo()).append("'");
				}
			});
		}

		if(!StringUtil.isNullOrEmpty(erp_no_sb.toString())){
			sqlBuilder.append(" AND r.erp_no in ( ").append(erp_no_sb.toString().substring(1)).append(" )");
			//arrayList.add(erp_no_sb.toString().substring(1));
		}
		sqlBuilder.append(" order by apply_date desc ");
		return this.findAllBySQL(TPatentInfoVo.class, sqlBuilder.toString(), null,false, null);
	}
}
